from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import pymysql


if __name__ == '__main__':
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='transportationdata', charset='utf8')
    plateData = pd.read_sql("select pass_time, monitor_id, direction from plate_table", con=conn)
    junctionRelationData = pd.read_sql("select monitor_id from junction_relation_table_t", con=conn)
    plateData = plateData.merge(junctionRelationData, on="monitor_id", how="left")
    plateData = plateData.dropna()

    plateData["pass_time"] = pd.to_datetime(plateData["pass_time"])
    # 选取时间为17：00至18：00的数据
    plateData = plateData[plateData["pass_time"].dt.hour.isin(np.arange(17, 18))]
    # 方向的流量计算
    flowData = pd.DataFrame(plateData.groupby(["monitor_id", "direction"], as_index=False).count())
    flowData = flowData.rename(columns={"pass_time": "flow"})

    engine = create_engine("mysql+pymysql://root:123456@localhost:3306/transportationdata?charset=utf8")
    flowData.to_sql("peek_time_flow_table", con=engine, if_exists="replace", index=False)

